

/*------------------------------------------------------------------------------
	1. Housekeeping
------------------------------------------------------------------------------*/

	*Run housekeeping code
		qui do ".../replication_package/housekeeping.do"

	*Write log
		log using "$log_loc/table_a1.log", text replace

/*------------------------------------------------------------------------------
	2. Prepare Census Data
------------------------------------------------------------------------------*/

	import excel using "$data/Census Count/nc-est2019-syasexn.xlsx", clear cellra(A6:M22)
		keep A K L M
		ren (A K L M) (age_num census_2017 census_2018 census_2019)

		tempfile census_data
		save `census_data', replace

		replace age_num = substr(age_num, 2, .)
		destring age_num, replace
		sort age_num

	reshape long census_, i(age_num) j(year)
		ren census_ census_ct
		isid year age_num

	tempfile census_dta
	save `census_dta', replace


/*------------------------------------------------------------------------------
	3. Main Data
------------------------------------------------------------------------------*/

		*Import Data
		use "$data/childrens2018.dta", clear

			/*Restrict Sample*/
				*Keep f1095 filings
					rename state_insurance state
					fmerge m:1 state using "$data/statelist.dta"
						assert _merge != 2 //Confirm that all 51 states are in dataset
						drop if _merge != 3

				*Keep if age_num is within the range
					keep if age_num >= 0 & age_num <= 16

				*Keep if kid died after end of year or is currently alive
					keep if 2018*10000+1231<death_date|death_date==0

				*Keep if months covered is possible
					keep if num_cov_ins > 0 & num_cov_ins <= 12
	
			/*Generate variables for table*/
				*Generate a count variable
					gen ct = 1

				*Generate year variable
					gen year = 2018

/*------------------------------------------------------------------------------
	4. Carry Out Estimated Calculations
------------------------------------------------------------------------------*/
			/*Prepare backup data*/
				*Collapse to the by-age_num level
					collapse (sum) ct, by(age_num year)

				*Merge in Census Count
					merge 1:1 age_num year using `census_dta'
						assert _merge != 1
						keep if _merge == 3
						drop _merge

				*Generate Overlap between 1095 and Census ct
					gen pct_share = ct/census_ct

			/*Organize backup data*/
				*Clean up dataset before export
					sort age_num
					order year age_num ct census_ct pct_share
			
		*Export backup data
		export excel using "$output/table_backup_data.xlsx", first(var) sheet(cover_1095_2018, replace)


	